#Loading packages
library(plotrix)
library(jtools)
library(sandwich)
library(sjPlot)
library(sjlabelled)
library(sjmisc)
library(ggplot2)
library(car)
library(lmtest)
library(multiwayvcov)
library(plyr)
library(estimatr)
library(dummies)
library(gridExtra)
library(dplyr)
library(tidyr)
library(broom)
library(readxl)
library(tidyverse)
library(readstata13)
library(haven)
library(cjoint)
library(dummies)
library(dplyr)
library(fastDummies)
library(cregg)
library(data.table)
library(Publish)
library(stargazer)
require(plm)
require(lmtest)
library(ggplot2)
library(patchwork)
library(lfe)
library(olsrr)
library(readstata13)
library(rlang)
library(estimatr)
library(texreg)
library(rlang)
library(haven)
library(foreign)
library(gghighlight)
library(cregg)
library(foreign)
library(multcomp)
library(emmeans)
library(tidyverse)
library(readxl)
library(xlsx)
library(openxlsx)


## Please set your directory here

conjoint_data = read.xlsx('councilors_nomiss.xlsx')

merged_data = conjoint_data[,39:69]

merged_data_profiles <- melt(data = merged_data, 
                             id.vars = c("ResponseId"),
                             variable.name = "responses")

merged_data_profiles$task_number <- str_sub(merged_data_profiles$responses, start = -3, end = -3)
merged_data_profiles$profile_number <- str_sub(merged_data_profiles$responses, start = -2, end = -2)
merged_data_profiles$attribute_number <- str_sub(merged_data_profiles$responses, start = -1, end = -1)

merged_data2 = conjoint_data[,24:39]

merged_data_attributes <- melt(data = merged_data2, 
                               id.vars = c("ResponseId"),
                               variable.name = "responses")

merged_data_attributes$task_number <- str_sub(merged_data_attributes$responses, start = -2, end = -2)
merged_data_attributes$attribute_number <- str_sub(merged_data_attributes$responses, start = -1, end = -1)


merged_data_attributes <- merged_data_attributes %>% rename(question=value)

data_questions_responses <- merged_data_attributes %>% dplyr::select('ResponseId', 'task_number', 'attribute_number', 'question') %>% left_join(merged_data_profiles, by=c("ResponseId","task_number","attribute_number"))

final_profile = dcast(data_questions_responses,
                      ResponseId + task_number + profile_number ~ question,
                      value.var = "value")
head(final_profile)

conjoint_data <- read.dta13("councilors_nomiss.dta")
greece_profiledata <- merge(final_profile, conjoint_data, by="ResponseId")

write.xlsx(greece_profiledata, "councilors_profiledata.xlsx")
